<?php
namespace app\admin\controller;
use think\Controller;
use app\model\Bill as BillModel;
// use app\model\BillTmp as BillModel;
use app\model\UserBill as UserBillModel;
use think\Db;

use app\lib\aliyun\Api as aliyunApi;



class Test extends Controller
{
    public function tongji01(){
        $list = Db::name('bill202208')->where([
            ['product_code','=','ecs'],
            ['subscription_type','=','Subscription']
        ])->column('DISTINCT instance_id');
        $data = [];
        // $data['total'] = count($list);
        foreach ($list as $k => $vo) {
            $instance_config = Db::name('bill202208')->where('instance_id',$vo)->order('instance_config desc')->value('instance_config');
            if ($instance_config == '-' || empty($instance_config)) {
                $data['未知']++;
            }else{
                preg_match("/实例规格:(((?!;).)*);/is",$instance_config,$instance);
                $data[$instance[1]]++;
            }
            
        }
        $total = count($list);
        $csv_data[] = ['ECS总数量',$total];
        foreach ($data as $k => $vo) {
            $csv_data[] = [$k,$vo,round($vo/$total*100,2).'%'];
        }
        exportCsv(date('YmdHis'),['规格','数量','占比'],$csv_data);
    } 


    // 将账单内容含有逗号的账单调整
    public function tiaozheng(){
        return false;
        $products = Db::name('product')->field('code,discount')->select();
        $products  = arrayParamExtract($products ,'code','discount');

        $table_name = 'bill';
        $list = Db::name($table_name)->where('currency','<>','CNY')->select();
        // dump($list);die;
        foreach ($list as $k => $vo) {
            // if (!empty($vo['admin_id'])) {
            //     $user_id = Db::name('user')->where('pk',$vo['ower_id'])->value('id');
            //     UserBillModel::saveData([
            //         'user_id'=>$user_id,
            //         'amount'=>-$vo['platform_amount'],
            //         'original'=>-$vo['pretax_gross_amount'],
            //         'type'=>0,
            //         'remark'=>'2022年8月账单补扣，订单号：'.$vo['related_order_id']
            //     ]);

            // }
            

            $save_data = [];
            $save_data['instance_config'] = $vo['instance_config'].'，'.$vo['instance_spec'];
            $save_data['instance_spec'] = $vo['public_ip'];
            $save_data['public_ip'] = $vo['private_ip'];
            $save_data['private_ip'] = $vo['region'];
            $save_data['region'] = $vo['zone'];
            $save_data['zone'] = '';
            $save_data['pretax_gross_amount'] = $vo['invoice_discount'];
            $save_data['invoice_discount'] = '';
            $save_data['pretax_amount'] = '';
            $save_data['currency'] = 'CNY';
            if (!empty($vo['admin_id'])) {
                if ($save_data['pretax_gross_amount'] < 0) { // 如果是退款，就按账单的金额 / 主账号的折扣（0.55） * 用户折扣
                    $product_discount = emptyDef($products[$vo['product_code']],1);
                    $save_data['platform_amount'] = round($save_data['pretax_gross_amount']/$product_discount*$vo['user_discount'],4);
                }else{
                    $save_data['platform_amount'] = round($save_data['pretax_gross_amount']*$vo['user_discount'],4);    
                }

                $user_id = Db::name('user')->where('pk',$vo['ower_id'])->value('id');
                UserBillModel::saveData([
                    'user_id'=>$user_id,
                    'amount'=>-$save_data['platform_amount'],
                    'original'=>-$save_data['pretax_gross_amount'],
                    'type'=>0,
                    'remark'=>'2022年9月账单补充，订单号：'.$vo['related_order_id']
                ]);
            }
            Db::name($table_name)->where('id',$vo['id'])->update($save_data);

        }
    }

    // 将没有按要求折扣扣费的账单调整
    public function tiaozheng2(){
        return false;
        $table_name = 'bill202208';
        $list = Db::name($table_name)->where([
            ['product_code','=','dcdn'],
            Db::raw('user_discount > 0 and user_discount < 1')
        ])->select();
        dump($list);die;
        $user = [];
        foreach ($list as $k => $vo) {
            if ( empty($user[$vo['ower_id']]) ) {
                $user[$vo['ower_id']] = 0;
            }
            $user[$vo['ower_id']] += $vo['pretax_gross_amount'] - $vo['platform_amount'];

            $save_data = [
                'user_discount'=>1,
                'platform_amount'=>$vo['pretax_gross_amount']
            ];
            // dump($save_data);die;
            Db::name($table_name)->where('id',$vo['id'])->update($save_data);
        }
        // foreach ($user as $ower_id => $vo) {
        //     $user_id = Db::name('user')->where('pk',$ower_id)->value('id');
        //     UserBillModel::saveData([
        //         'user_id'=>$user_id,
        //         'amount'=>-$vo,
        //         'type'=>0,
        //         'remark'=>'2022年8月DCDN折扣调整'
        //     ]);
        // }
    }

    // 将账单内容含有逗号的账单调整
    public function tiaozheng3(){
        return false;

        echo date('Y-m-d H:i:s');
        set_time_limit ( 0 );
        ini_set('memory_limit','10240M');

        $users = Db::name('user')->field('pk,total_discount')->where([
            Db::raw('pk is not null'),
            ['total_discount','<',1]
        ])->select();
        $user_pks = array_column($users, 'pk');
        $users  = arrayParamExtract($users ,'pk','total_discount');

        $table_name = 'bill';
        $list = Db::name($table_name)->where([
            ['product_code','=','disk'],
            ['admin_id','>',0],
            ['ower_id','in',$user_pks]
        ])->order('id asc')->select();
        $amounts = [];
        foreach ($list as $k => $vo) {
            if (!isset($amounts[$vo['ower_id']])) {
                $amounts[$vo['ower_id']] = 0;
            }
            $save_data = [];
            $save_data['user_discount'] = $users[$vo['ower_id']];
            $save_data['platform_amount'] = round($vo['pretax_gross_amount']*$save_data['user_discount'],4); 
            // $different = bcsub($vo['platform_amount'],$save_data['platform_amount'],4);
            // $amounts[$vo['ower_id']] = bcadd($amounts[$vo['ower_id']], $different,4);
            if ($k % 2000 == 1) {
                Db::name('test')->insert(['content'=>'-----1------'.$vo['id']]);
            }

            Db::name($table_name)->where('id',$vo['id'])->update($save_data);
            // dump($vo);
            // dump($users[$vo['ower_id']]);
            // dump($save_data);die;

            // die;
            
            // if ($save_data['pretax_gross_amount'] < 0) { // 如果是退款，就按账单的金额 / 主账号的折扣（0.55） * 用户折扣
            //     $product_discount = emptyDef($products[$vo['product_code']],1);
            //     $save_data['platform_amount'] = round($save_data['pretax_gross_amount']/$product_discount*$vo['user_discount'],4);
            // }else{
            //     $save_data['platform_amount'] = round($save_data['pretax_gross_amount']*$vo['user_discount'],4);    
            // }

            // $user_id = Db::name('user')->where('pk',$vo['ower_id'])->value('id');
            // UserBillModel::saveData([
            //     'user_id'=>$user_id,
            //     'amount'=>-$save_data['platform_amount'],
            //     'original'=>-$save_data['pretax_gross_amount'],
            //     'type'=>0,
            //     'remark'=>'2022年9月账单补充，订单号：'.$vo['related_order_id']
            // ]);
            // Db::name($table_name)->where('id',$vo['id'])->update($save_data);
        }
        

        // foreach ($amounts as $pk => $amount) {
        //     $user_id = Db::name('user')->where('pk',$pk)->value('id');
        //     UserBillModel::saveData([
        //         'user_id'=>$user_id,
        //         'amount'=>$amount,
        //         'type'=>0,
        //         'remark'=>'2022年9月块存储扣费调整'
        //     ]);
        // }
        Db::name('test')->insert(['content'=>'-----1------'.date('Y-m-d H:i:s')]);
        dump(date('Y-m-d H:i:s'));die;
    }

    public function product11(){
        $dsn_config = config('config.dsn_config');
        $products = Db::connect($dsn_config)->table('product_info')->select();

        $list = [];
        foreach ($products as $k => $product) {
            if ( Db::name('product')->where('code',$product['code'])->value('discount') != $product['discount'] ) {
                $list[] = [
                    $product['name'],
                    $product['code'],
                    $product['discount'],
                    Db::name('product')->where('code',$product['code'])->value('discount')
                ];
            }

        }
        exportCsv(date('YmdHis'),['产品名称','编码','贝多折扣','得力云折扣'],$list);
        // Db::name('product')->insertAll($list);
        dump($list);die;
    }

    public function balanceCheck(){
        set_time_limit ( 0 );
        ini_set('memory_limit','10240M');

        $list = Db::name('user')->where([
            ['balance','<>',0],
            Db::raw('pk is not null'),
            ['id','>',984],
            ['mobile','<>','18328501481']
        ])->order('id','asc')->select();
        // dump($list);die;
        $data = [];
        foreach ($list as $user) {
            dump($user['mobile']);
            $api_controller = new aliyunApi();
            // 查询剩余余额
            $quota = $api_controller->QueryResellerAvailableQuota($user['pk']);
            // $quota = json_decode($quota,true);
            $quota = $quota['Data'];
            $true_balance = round($user['balance']/$user['total_discount'],4);
            if (abs($quota - $true_balance) > 1000) {
                // $data[] = [
                //     'mobile'=>$user['mobile'],
                //     'name'=>$user['name'],
                //     'balance'=>$user['balance'],
                //     'quota'=>$quota
                // ];
                $data = [
                    'id'=>$user['id'],
                    'mobile'=>$user['mobile'],
                    'name'=>$user['name'],
                    'balance'=>$user['balance'],
                    'true_balance'=>$true_balance,
                    'quota'=>$quota,
                    'different'=>$quota - $true_balance
                ];
                Db::name('test')->insert(['content'=>json_encode($data,JSON_UNESCAPED_UNICODE)]);
            }
        }
        Db::name('test')->insert(['content'=>'123456']);
        // dump($data);die;
        echo 'success';die;
    }

    public function balanceCheck2(){
        $list = Db::name('test')->where('id','<>',1)->column('content');
        foreach ($list as $k => $vo) {
            $vo = json_decode($vo,true);
            // if ($vo['balance'] < 0 && $vo['quota'] > 0) {
            //     $data[] = $vo;
            // }
            if ($vo['true_balance'] < $vo['quota']) {
                // $data[] = $vo['mobile'];
                $data[] = $vo;
            }
        }
        dump($data);die;

    }

    // 调整user_bill的余额列
    public function tzuserbill(){
        $ower_ids = [];
        // $ower_ids = ['1737951674445458'];
        foreach ($ower_ids as $ower_id) {
            $user_id = Db::name('user')->where('pk',$ower_id)->value('id');
            $user_bills = Db::name('user_bill')->where('user_id',$user_id)->order('id','asc')->select();
            // dump($user_bills);die;
            $balance = 0;
            foreach ($user_bills as $k => $bill) {
                $balance = $balance + $bill['amount'];
                Db::name('user_bill')->where('id',$bill['id'])->update(['balance'=>$balance]);
            }
        }
    }


    // 调整user_discount和platform_amount互换的情况
    public function tzbillhuhuan(){
        return false;
        $list = Db::name('bill')->where([
            Db::raw('user_discount > 1 or user_discount < 0')
        ])->select();
        $user_bills = [];

        // 给用户记账
        Db::startTrans();
        try {
            foreach ($list as $k => $tmp) {
                $save_data = [
                    'user_discount'=>$tmp['platform_amount'],
                ];
                $save_data['platform_amount'] = round($tmp['pretax_gross_amount']*$save_data['user_discount'],4);
                if ( Db::name('bill')->where('id',$tmp['id'])->update($save_data) === false ) {
                    throw new \Exception("更新bill失败");
                }


                $different = $save_data['platform_amount'] - $tmp['platform_amount'];
                $ctime = date('Y-m-d',$tmp['create_time']);
                $user_bills[$tmp['ower_id']][$ctime]['different'] += $different;
            }
            // dump($user_bills);die;

            foreach ($user_bills as $ower_id => $bills) {
                $user_id = Db::name('user')->where('pk',$ower_id)->value('id');
                foreach ($bills as $bill_date => $different) {
                    $ub = Db::name('user_bill')->where([
                        ['ctime','between',[strtotime($bill_date),strtotime($bill_date.' 23:59:59')]],
                        ['user_id','=',$user_id],
                        ['remark','like','%账单扣费%']
                    ])->find();
                    if (empty($ub)) {
                        throw new \Exception("ub不存在");
                    }


                    $different = $different['different'];
                    if (Db::name('user_bill')->where('id',$ub['id'])->inc('amount',$different)->inc('balance',$different)->update() === false) {
                        throw new \Exception("更新UB失败");
                    }
                    if (Db::name('user')->where('id',$ub['user_id'])->inc('balance',$different)->update() === false) {
                        throw new \Exception("更新用户余额失败");
                    }
                }
            }
            Db::commit();
        } catch (\Exception $e) {
            // 更新失败 回滚事务
            Db::rollback();
            echo 'error';
            exit();
        }
    }


    // public function duibi_ub(){
    //     set_time_limit ( 0 );
    //     ini_set('memory_limit','10240M');


    //     $list = Db::name('tmp_user_bill')->field('id,user_id,amount,remark,original,difference')->select();
    //     // dump($list);die;
    //     // $datas = [];
    //     foreach ($list as $k => $vo) {
    //         $data = Db::name('user_bill')->field('id,user_id,amount,remark,original,difference')->where([
    //             ['user_id','=',$vo['user_id']],
    //             ['remark','=',$vo['remark']]
    //         ])->find();
    //         if (!empty($data)) {
    //             if ( Db::name('user_bill')->where('id',$data['id'])->update([
    //                 'amount'=>$vo['amount'],
    //                 'original'=>$vo['original'],
    //                 'difference'=>$vo['difference']
    //             ]) !== false) {
    //                 Db::name('tmp_user_bill')->where('id',$vo['id'])->delete();
    //             }
    //         }
            
    //     }

    //     Db::name('test')->insert(['content'=>'----------success']);
    //     // dump($datas);die;
    //     echo '111';
    // }
    public function duibi_ub(){
        set_time_limit ( 0 );
        ini_set('memory_limit','10240M');


        $list = Db::name('tmp_user_bill_copy1')->field('id,user_id,amount,remark,original,difference')->where('user_id',103)->select();
        // dump($list);die;
        $datas = [];
        foreach ($list as $k => $vo) {
            $data = Db::name('user_bill_back')->field('id,user_id,amount,remark,original,difference')->where([
                ['user_id','=',$vo['user_id']],
                ['remark','=',$vo['remark']]
            ])->find();
            if (!empty($data) && abs($data['amount'] - $vo['amount']) >= 100) {
                $datas[] = [
                    $vo,$data
                ];
            }
            
        }

        // Db::name('test')->insert(['content'=>'----------success']);
        dump($datas);die;
        echo '111';
    }

    // public function ub_balance(){
    //     set_time_limit ( 0 );
    //     ini_set('memory_limit','10240M');

    //     $list = Db::name('user')->field('id,mobile,balance')->where([
    //         Db::raw('pk is not null'),
    //         ['balance','<>',0],
    //         ['mobile','in',['13266895833','13058059118','15622751186','17762135580','15985738311','15350300618']]
    //     ])->select();
    //     $datas = [];
    //     foreach ($list as $k => $vo) {
    //         $ub_balance = Db::name('user_bill')->where('user_id',$vo['id'])->sum('amount');
    //         if ($vo['balance'] != $ub_balance) {
    //             $vo['ub_balance'] = $ub_balance;
    //             $vo['different'] = floatval($vo['balance']) - $ub_balance;
    //             $datas[] = $vo;
    //         }
    //     }
    //     // Db::name('test')->insert(['content'=>json_encode($datas)]);
    //     dump($datas);die;
    //     echo '111';
    // }
    public function ub_balance(){
        set_time_limit ( 0 );
        ini_set('memory_limit','10240M');

        $list = Db::name('user')->field('id,mobile,balance')->where([
            Db::raw('pk is not null'),
            ['balance','<>',0],
            // ['id','in',[178,543,738]]
            // ['mobile','in',['']]
            // ['mobile','in',['17762135580','15985738311','15350300618']]
        ])->select();
        // dump($list);die;
        $datas = [];
        foreach ($list as $k => $vo) {
            $ub_balance = Db::name('user_bill')->where('user_id',$vo['id'])->sum('amount');
            if ($vo['balance'] != $ub_balance) {
                // Db::name('user')->where('id',$vo['id'])->update(['balance'=>$ub_balance]);
                $vo['ub_balance'] = $ub_balance;
                $vo['different'] = floatval($vo['balance']) - $ub_balance;
                $datas[] = $vo;
            }
        }
        // Db::name('test')->insert(['content'=>json_encode($datas)]);
        dump($datas);die;
        echo '111';
    }

    public function heduibill(){
        set_time_limit ( 0 );
        ini_set('memory_limit','10240M');

        $show_data = [];
        $bill_date = '20221001';
        while ($bill_date < 20221117) {
            

            $object = config('config.system.ali_uid').'_InstanceDetail_'.$bill_date;

            $localpath = config('config.aliyun_oss.localpath');
            if (file_exists($localpath.$object.'.csv')) {
                $file_type = 'csv';
            }elseif (file_exists($localpath.$object.'.zip')) {
                $file_type = 'zip';
            }
            $csv_file = $localpath.$object.'.csv';
            $data = [];
            $handle = fopen($csv_file,'r');
            while(($line = fgets($handle)) !== FALSE) { 
                $data[] = $line;
            } 
            fclose($handle);
            array_shift($data);

            $start_time = strtotime($bill_date);
            $end_time = strtotime('+1 days',$start_time) - 1;
            $bill_count = Db::name('bill')->where([
                ['payment_time','between',[$start_time,$end_time]]
            ])->count();
            if ($bill_count != count($data)) {
                $show_data[] = [
                    'bill_date'=>$bill_date,
                    'bill'=>$bill_count,
                    'csv'=>count($data),
                    'difference'=>$bill_count - count($data)
                ];
            }
            $bill_date = date('Ymd',strtotime('+1 days',$start_time));
        }
        Db::name('test')->insert(['content'=>json_encode($show_data)]);
        dump($show_data);die;

    }


    public function heduibalance(){
        $user_id = 192;
        $data = Db::name('user')->where('id',$user_id)->find();
        dump($data);

        $bill_amount = [];
        $bill_amount[] = Db::name('bill202206')->where('ower_id',$data['pk'])->sum('platform_amount');
        $bill_amount[] = Db::name('bill202207')->where('ower_id',$data['pk'])->sum('platform_amount');
        $bill_amount[] = Db::name('bill202208')->where('ower_id',$data['pk'])->sum('platform_amount');
        $bill_amount[] = Db::name('bill202209')->where('ower_id',$data['pk'])->sum('platform_amount');
        $bill_amount[] = Db::name('bill')->where('ower_id',$data['pk'])->sum('platform_amount');

        dump($bill_amount);

        $result = [];
        $result['all_bill'] = array_sum($bill_amount);
        $result['all_ub'] = Db::name('user_bill')->where('user_id',$user_id)->sum('amount');
        $result['all_recharge'] = Db::name('user_bill')->where([
            ['user_id','=',$user_id],
            ['type','=',1]
        ])->sum('amount');
        // $result['all_refund'] = Db::name('user_bill')->where([
        //     ['user_id','=',$user_id],
        //     ['ctime','>',1665590400],
        //     ['remark','like','%退费订单调整%']
        // ])->sum('amount');

        // $result['true_balance'] = $result['all_recharge'] - $result['all_bill'] + $result['all_refund'];
        $result['true_balance2'] = $result['all_recharge'] - $result['all_bill'];
        dump($result);
    }

    public function heduibalance_detail2(){
        $user_id = 216;
        $data = Db::name('user')->where('id',$user_id)->find();

        $start_time = strtotime('2022-11-01');
        $bill_amount = Db::name('bill')->where([
            ['ower_id','=',$data['pk']],
            // ['payment_time','between',[$start_time,$end_time]]
            ['payment_time','>',$start_time]
        ])->sum('platform_amount');

        $ub_amount = Db::name('user_bill')->where([
            ['type','=',0],
            ['user_id','=',$user_id],
            ['remark','like','%账单扣费%'],
            ['id','>=',17449]
        ])->sum('amount');

        dump($bill_amount);
        dump($ub_amount);die;
    }

    public function heduibalance_detail(){
        $user_id = 5128;
        $data = Db::name('user')->where('id',$user_id)->find();

        $ub_list = Db::name('user_bill')->where([
            ['type','=',0],
            ['user_id','=',$user_id],
            ['remark','like','%账单扣费%'],

            // ['id','=',20059]
        ])->select();
        foreach ($ub_list as $k => $ub) {

            $ub_date = intval($ub['remark']);
            if (empty($ub_date)) {
                continue;
            }
            // $start_time = strtotime(date('Y-m-d',$ub['ctime']));
            $start_time = strtotime($ub_date);
            $end_time = strtotime('+1 days',$start_time) - 1;
            // dump($start_time);
            // dump($end_time);
            // dump($ub);die;
            $ub_date = date('Ym',strtotime($ub_date));

            $table_name = config('database.prefix').'bill'.$ub_date;
            $isTable = db()->query('SHOW TABLES LIKE '."'".$table_name."'");
            if (empty($isTable)) {
                $ub_date = '';
            }
            
            $platform_amount = Db::name('bill'.$ub_date)->where([
                ['ower_id','=',$data['pk']],
                ['payment_time','between',[$start_time,$end_time]]
                // ['create_time','between',[$start_time,$end_time]]
            ])->sum('platform_amount');
            if ($platform_amount + $ub['amount'] != 0) {
                dump([
                ['ower_id','=',$data['pk']],
                ['payment_time','between',[$start_time,$end_time]]
                // ['create_time','between',[$start_time,$end_time]]
            ]);die;
                $ub['platform_amount'] = $platform_amount;
                dump($ub);
            }
            
        }
        echo 'success';die;
        dump($ub_list);die;
    }

    // public function getbilltime(){
    //     $list = Db::name('user_bill')->where([
    //         ['remark','like','%退费订单调整%']
    //     ])->select();
    //     dump(count($list));
    //     $regex = '/退费订单调整，订单号：(\d+)，原账单费用/';
    //     foreach ($list as $k => $vo) {
    //         preg_match($regex, $vo['remark'], $matches);
    //         $order_id = $matches[1];
    //         $ctime = Db::name('bill')->where('order_id',$order_id)->value('create_time');
    //         // dump($ctime);die;
    //         if (empty($ctime)) {
    //             $ctime = Db::name('bill202209')->where('order_id',$order_id)->value('create_time');
    //         }
    //         if ($ctime >= 1665676800) {
    //             $vo['bill_data'] = $ctime;
    //             $data[] = $vo;
    //         }

    //         # code...
    //     }
    //     dump($data);die;
    // }




   
}
